package com.sailfish.springbootdemo.dao.db2;

import com.sailfish.springbootdemo.common.DB2TableName;
import com.sailfish.springbootdemo.pojo.db2.PcbCapacityDataApprove;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface PcbCapacityDataApproveDao {
    @Results(id = "pcbCapacityDataApproveResultMap", value = {
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER),
            @Result(column = "flow_num", property = "flowNum", jdbcType = JdbcType.VARCHAR),
            @Result(column = "seq", property = "seq", jdbcType = JdbcType.INTEGER),
            @Result(column = "flow_type", property = "flowType", jdbcType = JdbcType.INTEGER),
            @Result(column = "parent_factory_id", property = "parentFactoryId", jdbcType = JdbcType.INTEGER),
            @Result(column = "factory_id", property = "factoryId", jdbcType = JdbcType.INTEGER),
            @Result(column = "approve_status", property = "approveStatus", jdbcType = JdbcType.VARCHAR),
            @Result(column = "remark", property = "remark", jdbcType = JdbcType.VARCHAR),
            @Result(column = "approve_user_id", property = "approveUserId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "submit_time", property = "submitTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "create_user", property = "createUser", jdbcType = JdbcType.VARCHAR),
            @Result(column = "update_user", property = "updateUser", jdbcType = JdbcType.VARCHAR),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "sort", property = "sort", jdbcType = JdbcType.INTEGER),
            @Result(column = "row_key", property = "rowKey", jdbcType = JdbcType.VARCHAR)
    })

    /*
     *查询单个
     */
    @Select("<script> " +
            "select " +
            "id, flow_num, seq, flow_type, parent_factory_id, factory_id, approve_status, remark, approve_user_id, create_user, create_time, update_user, update_time, status, sort,row_key " +
            "from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +
            " where id = #{id}" +
            "</script>")
    PcbCapacityDataApprove getById(@Param("id") Integer id);

    /*查询单个*/
    @Select("<script> " +
            "select " +
            "id, flow_num, seq, flow_type, parent_factory_id, factory_id, approve_status, remark, approve_user_id, create_user, create_time, update_user, update_time, status, sort, row_key " +
            "from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +
            " where flow_num like concat(#{flowDay},'%') " +
            " order by seq desc LIMIT 1" +
            "</script>")
    @ResultMap(value = "pcbCapacityDataApproveResultMap")
    PcbCapacityDataApprove getByFlowNum(@Param("flowDay") String flowDay);

    /*按条件查询*/
    @Select("<script> " +
            "select " +
            "a.id, a.flow_num, a.seq, a.flow_type, a.parent_factory_id, a.factory_id, a.approve_status, a.remark, a.approve_user_id, a.create_user, a.create_time, a.update_user, a.update_time, a.status, a.sort, a.row_key,b.update_time AS submit_time " +
            "from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +" a " +
            "left join "+ DB2TableName.PCB_CAPAICTY_APPROVE_DETAIL +" b ON a.id=b.data_id AND a.flow_type=b.flow_type AND (b.approve_node = '厂家处理' || b.approve_node = '厂家确认') " +
            "<where>" +
            "<if test='parentFactoryId != null'>" +
            " and a.parent_factory_id = #{parentFactoryId}" +
            "</if>" +
            "<if test='factoryId != null'>" +
            " and a.factory_id = #{factoryId}" +
            "</if>" +
            "<if test='flowType != null'>" +
            " and a.flow_type = #{flowType}" +
            "</if>" +
            "<if test='approveStatus != null and approveStatus != \"\"'>" +
            " and a.approve_status = #{approveStatus}" +
            "</if>" +
            "<if test='createUser != null'>" +
            " and a.create_user = #{createUser}" +
            "</if>" +
            "<if test='status == 1'>" +
            " and (a.`status` != 2 or a.`status` is null)" +
            "</if>" +
            "<if test='rowKey != null and rowKey != \"\"'>" +
            " and a.row_key = #{rowKey}" +
            "</if>" +
            "<if test='approveUserId != null and approveUserId != \"\"'>" +
            " and a.approve_user_id = #{approveUserId}" +
            "</if>" +
            " and a.factory_id  != 0 " +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbCapacityDataApproveResultMap")
    List<PcbCapacityDataApprove> getByLimit(PcbCapacityDataApprove param);

    @Select("select * from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE
            + " where (`status` != 2 or `status` is null) " +
            "and (flow_type = 3 or flow_type = 5) " +
            "and (factory_id = #{factoryId})")
    @ResultMap(value = "pcbCapacityDataApproveResultMap")
    List<PcbCapacityDataApprove> getTwoTypes(@Param("factoryId") Integer factoryId);

    /*按条件查询*/
    @Select("<script> " +
            "select " +
            "a.id, a.flow_num, a.seq, a.flow_type, a.parent_factory_id, a.factory_id, a.approve_status, a.remark, a.approve_user_id, a.create_user, a.create_time, a.update_user, a.update_time, a.status, a.sort, a.row_key,b.update_time AS submit_time " +
            "from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +" a " +
            "left join "+ DB2TableName.PCB_CAPAICTY_APPROVE_DETAIL +" b ON a.id=b.data_id AND a.flow_type=b.flow_type AND (b.approve_node = '厂家处理' || b.approve_node = '厂家确认') " +
            "<where>" +
            "<if test='parentFactoryId != null'>" +
            " and a.parent_factory_id = #{parentFactoryId}" +
            "</if>" +
            "<if test='factoryId != null'>" +
            " and a.factory_id = #{factoryId}" +
            "</if>" +
            "<if test='flowType != null'>" +
            " and a.flow_type = #{flowType}" +
            "</if>" +
            "<if test='approveStatus != null and approveStatus != \"\"'>" +
            " and a.approve_status = #{approveStatus}" +
            "</if>" +
            "<if test='approveStatus == null or approveStatus == \"\"'>" +
            " and a.approve_status != '已发布'" +
            "</if>" +
            "<if test='createUser != null'>" +
            " and a.create_user = #{createUser}" +
            "</if>" +
            "<if test='status == 1'>" +
            " and (a.`status` != 2 or a.`status` is null)" +
            "</if>" +
            "<if test='rowKey != null and rowKey != \"\"'>" +
            " and a.row_key = #{rowKey}" +
            "</if>" +
            "<if test='approveUserId != null and approveUserId != \"\"'>" +
            " and a.approve_user_id = #{approveUserId}" +
            "</if>" +
            " and a.factory_id  != 0 " +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbCapacityDataApproveResultMap")
    List<PcbCapacityDataApprove> getByLimit2(PcbCapacityDataApprove param);

    @Select("<script> " +
            "select " +
            "id, flow_num, seq, flow_type, parent_factory_id, factory_id, approve_status, remark, approve_user_id, create_user, create_time, update_user, update_time, status, sort,row_key " +
            "from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +
            "<where>" +
            " and factory_id = 0 " +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbCapacityDataApproveResultMap")
    PcbCapacityDataApprove getSystemStatus();

    @Select("<script> " +
            "select " +
            "id, flow_num, seq, flow_type, parent_factory_id, factory_id, approve_status, remark, approve_user_id, create_user, create_time, update_user, update_time, status, sort,row_key " +
            "from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +
            "<where>" +
            " and approve_status != '已发布' " +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbCapacityDataApproveResultMap")
    List<PcbCapacityDataApprove> getNotComplete();

    /*
     *新增所有列
     */
    @Insert("insert into " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +
            " ( flow_num, seq, flow_type, parent_factory_id, factory_id, approve_status, remark, approve_user_id, create_user, create_time, update_user, update_time, status, sort,row_key) values " +
            "( #{flowNum}, #{seq}, #{flowType}, #{parentFactoryId}, #{factoryId}, #{approveStatus},#{remark},#{approveUserId}, #{createUser}, #{createTime}, #{updateUser}, #{updateTime}, #{status}, #{sort}, #{rowKey})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insert(PcbCapacityDataApprove param);

    @Delete("delete from " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE + " where id = #{id}  ")
    Integer deleteById(Integer id);

    /*
     *通过主键修改数据
     */
    @Update("<script>" +
            "update " + DB2TableName.PCB_CAPAICTY_DATA_APPROVE +
            "<set>" +
            "<if test='parentFactoryId != null'>" +
            " parent_factory_id = #{parentFactoryId}," +
            "</if>" +
            "<if test='factoryId != null '>" +
            "    factory_id = #{factoryId}," +
            "</if>" +
            "<if test='approveStatus != null and approveStatus != \"\" '>" +
            "    approve_status = #{approveStatus}," +
            "</if>" +
            "<if test='remark != null and remark != \"\" '>" +
            "    remark = #{remark}," +
            "</if>" +
            "<if test='approveUserId != null and approveUserId != \"\" '>" +
            "    approve_user_id = #{approveUserId}," +
            "</if>" +
            "<if test='sort != null'>" +
            "    sort = #{sort}," +
            "</if>" +
            "<if test='status != null'>" +
            "    status = #{status}," +
            "</if>" +
            "<if test='createTime != null'>" +
            "    create_time = #{createTime}," +
            "</if>" +
            "<if test='updateTime != null'>" +
            "    update_time = #{updateTime}," +
            "</if>" +
            "<if test='createUser != null and createUser != \"\"'>" +
            "    create_user = #{createUser}," +
            "</if>" +
            "<if test='updateUser != null and updateUser != \"\"'>" +
            "    update_user = #{updateUser}," +
            "</if>" +
            "<if test='rowKey != null and rowKey != \"\"'>" +
            "    row_key = #{rowKey}," +
            "</if>" +
            "</set>" +
            "where id = #{id}" +
            "</script>")
    Integer update(PcbCapacityDataApprove param);
}
